<?php

namespace Biz\pockets\excel;

use PHPExcel;
use PHPExcel_CachedObjectStorageFactory;
use PHPExcel_Cell_DataType;
use PHPExcel_IOFactory;
use PHPExcel_Settings;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_NumberFormat;

class DownExcel
{
    private $excelObj;
    private $fileName = 'download.xls';
    public $saveType = 0;//0:php://output 1:file

    const FORMAT_CURRENCY_USD_SIMPLE = '"￥"#,##0.00_-';

    public function __construct()
    {
        // 先取消原框架的自动注册机制,避免和excel自动加载机制冲突
        spl_autoload_unregister('autoload');

        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip; //将单元格序列化后进行gzip压缩存在内存
        $cacheSettings = ['memoryCacheSize' => '200M'];
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        $this->excelObj = new PHPExcel();
    }

    /**
     * 设置下载时文件名
     * @param string $fileName
     */
    public function setFileName($fileName)
    {
        if (isset($_SERVER['HTTP_USER_AGENT'])) {
            //解决IE下乱码问题
            if (strpos($_SERVER['HTTP_USER_AGENT'], 'Trident')) {
                $fileName = urlencode($fileName);
                iconv('UTF-8', 'GBK//IGNORE', $fileName);
            }
        }
        $this->fileName = $fileName;
    }

    /**
     * 根据总数,返回列数组
     *
     * @param int $count
     * @return array
     */
    private function getCharByNumber($data)
    {
        // 自动减去头部
        $count = count($data ['0']);
        $keys = array();
        for ($number = 1; $number <= $count; $number++) {
            $divisor = intval($number / 26);
            if ($number % 26 == 0 && $divisor > 0) {
                $char = chr(64 + $number);
            } else {
                $char = chr(64 + $number % 26);
                $char = ($divisor == 0) ? $char : chr(64 + $divisor) . $char;
            }
            $keys [] = $char;
        }
        return $keys;
    }

    /**
     * 生成Excel表格
     * @param $data @填充的二维数据
     * @param array $header header头 可支持一维数组也可支持二维数组
     * @param null $replace 要替换的数组
     * @param string $index 合并的主键 如以order_no为主键
     * @param array $mergeCount 合并间距数组 如：['order_no1'=>3,'order_no2'=>5]
     * @param array $mergeCell 合并的列 如：['A','B','C','D']
     * @param array $mergeHeader ['A1:A2', 'B1:B2', 'C1:C2', 'D1:E1', 'F1:F2', 'G1:G2', 'H1:H2', 'I1:I2', 'J1:J2']
     * @return bool
     */
    public function create($data, $header = array(), $replace = null, $index = '', $mergeCount = array(), $mergeCell = array(), $mergeHeader = array(), $stringArray = array())
    {
        if (empty($data))
            return false;
        if (empty($data))
            return false;
        if (count($header) == count($header, 1)) {
            $flag = true; //一维数组
        } else {
            $flag = false; //多维数组
        }
        $keys = $this->getCharByNumber($data);
        $this->createHeader($keys, $header, $flag, $mergeHeader);
        $j = 0;
        if ($flag) {
            $startRowNum = 2; //默认从A2开始放数据
        } else {
            $startRowNum = count($header) + 1; //从第几行开始放数据
        }
        foreach ($data as $i => $vo) {
            $j = 0;
            foreach ($vo as $key => $item) {
                if (isset ($replace [$key])) {
                    if (in_array($keys[$j], $stringArray) && !empty($stringArray)) {
                        $this->excelObj->getActiveSheet()->setCellValueExplicit($keys [$j] . ($i + $startRowNum), $replace [$key] [$item], PHPExcel_Cell_DataType::TYPE_STRING);
                    } else {
                        $this->excelObj->setActiveSheetIndex(0)->setCellValue($keys [$j] . ($i + $startRowNum), $replace [$key] [$item]);
                    }
                } else {
                    if (in_array($keys[$j], $stringArray) && !empty($stringArray)) {
                        $this->excelObj->getActiveSheet()->setCellValueExplicit($keys [$j] . ($i + $startRowNum), $item, PHPExcel_Cell_DataType::TYPE_STRING);
                    } else {
                        $this->excelObj->setActiveSheetIndex(0)->setCellValue($keys [$j] . ($i + $startRowNum), $item);
                    }
                }
                $this->setWrapText($keys [$j] . ($i + $startRowNum));
                ++$j;
            }
        }

        if (!empty($index)) {
            foreach ($mergeCount as $indexValue => $cell) {
                $mergeIndex = '';
                foreach ($data as $key => $value) {
                    if ($indexValue == $value[$index]) {
                        $mergeIndex = $key;
                        break;
                    }
                }
                //纵向合并单元格 （某一行整体操作的）
                foreach ($mergeCell as $merge) {
                    //var_dump($merge . ($mergeIndex + 2) . ":$merge" . ($mergeIndex + 1 + $cell));
                    $this->setMergeCells($merge . ($mergeIndex + $startRowNum) . ":$merge" . ($mergeIndex + $startRowNum + $cell - 1));
                }
            }
        }

        if ($this->saveType == 0) {
            //输出到临时缓冲区  提供下载
            ob_end_clean();
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
            header("Content-Type:application/force-download");
            header("Content-Type:application/vnd.ms-execl");
            header("Content-Type:application/octet-stream");
            header("Content-Type:application/download");
            header("Content-Disposition:attachment;filename=" . $this->fileName . ".xls");
            header("Content-Transfer-Encoding:binary");
            $objWriter = PHPExcel_IOFactory::createWriter($this->excelObj, 'Excel5');
            $objWriter->save('php://output');
        } else if ($this->saveType == 1) {
            $objWriter = PHPExcel_IOFactory::createWriter($this->excelObj, 'Excel5');
            $tempDir = sys_get_temp_dir() . DIRECTORY_SEPARATOR;
            $file = $tempDir . $this->fileName . time() . '.xls';
            $objWriter->save($file);
            return $file;
        }
    }

    /**
     * 创建头部
     *
     * $header = [
     * ['订单号', '快递编号', '快递单号', '收件人姓名', '收件人电话', '省份', '市区', '区县', '具体地址', '订单商品'],
     * ['订单号', '快递编号', '快递单号', 'hahah', 'aaaaaa', '省份', '市区', '区县', '具体地址', '订单商品'],
     * ];
     * $mergeHeader = ['A1:A2', 'B1:B2', 'C1:C2', 'D1:E1', 'F1:F2', 'G1:G2', 'H1:H2', 'I1:I2', 'J1:J2'];
     * @param array $headers =[$header,$header....]
     */
    private function createHeader($keys, $headers, $flag, $mergeHeader)
    {
        $rows = count($headers); //header头的间距
        if ($flag) {
            $headers = array_combine($keys, $headers);
            foreach ($headers as $key => $vo) {
                $this->excelObj->setActiveSheetIndex(0)->setCellValue("{$key}1", $vo);
            }
        } else {
            for ($i = 0; $i < $rows; $i++) {
                $newKeys = array_map(function ($v) use ($i) {
                    return $v . ($i + 1);
                }, $keys);
                $newHeaders = array_combine($newKeys, $headers[$i]);
                foreach ($newHeaders as $key => $vo) {
                    $this->excelObj->setActiveSheetIndex(0)->setCellValue("{$key}", $vo);
                }
            }
        }

        if (!empty($mergeHeader)) {
            foreach ($mergeHeader as $mergeCell) {
                $this->setMergeCells($mergeCell);
            }
        }

    }

    /**
     * 设置sheet标题
     * @param string $title
     */
    public function setSheetTitle($title)
    {
        $this->excelObj->getActiveSheet()->setTitle($title);
    }

    /**
     * 设置标题
     * @param $title
     */
    public function setTitle($title)
    {
        $this->excelObj->getProperties()->setTitle($title);
    }

    /**
     * 设置创建人
     * @param $creator
     */
    public function setCreator($creator)
    {
        $this->excelObj->getProperties()->setCreator($creator);
    }

    /**
     * 设置修改人
     * @param $reviser
     */
    public function setReviser($reviser)
    {
        $this->excelObj->getProperties()->setLastModifiedBy($reviser);
    }

    /**
     * 设置题目
     * @param $subject
     */
    public function setSubject($subject)
    {
        $this->excelObj->getProperties()->setSubject($subject);
    }

    /**
     * 设置描述
     * @param $description
     */
    public function setDescription($description)
    {
        $this->excelObj->getProperties()->setDescription($description);
    }

    /**
     * 设置关键字
     * @param $keywords
     */
    public function setKeywords($keywords)
    {
        $this->excelObj->getProperties()->setKeywords($keywords);
    }

    /**
     * 设置种类
     * @param $category
     */
    public function setCategory($category)
    {
        $this->excelObj->getProperties()->setCategory($category);
    }

    /**
     * 设置宽度
     * @param array $array
     */
    public function setWidth($array)
    {
        foreach ($array as $key => $value) {
            $this->excelObj->getActiveSheet()->getColumnDimension($key)->setWidth($value);
        }
    }

    /**
     * 设置所有单元格默认高度
     * @param $height
     */
    public function setDefaultRowsHeight($height)
    {
        $this->excelObj->getActiveSheet()->getDefaultRowDimension()->setRowHeight($height);
    }

    /**
     * 设置行高
     * @param $array
     * @return bool
     */
    public function setRowsHeight($array)
    {
        if (empty($array))
            return false;

        foreach ($array as $key => $value) {
            $this->excelObj->getActiveSheet()->getRowDimension($key)->setRowHeight($value);
        }
    }

    /**
     * 设置字体名称
     * @param $index
     * @param string $select
     */
    public function setFrontName($index, $select = '微软雅黑')
    {
        $this->excelObj->getActiveSheet()->getStyle($index)->getFont()->setName($select);
    }

    /**
     * 设置字体大小
     * @param $index
     * @param string $select
     */
    public function setFrontSize($index, $select = '12')
    {
        $this->excelObj->getActiveSheet()->getStyle($index)->getFont()->setSize($select);
    }

    /**
     * 设置字体是否加粗
     * @param $index
     * @param bool $select
     */
    public function setFrontBold($index, $select = true)
    {
        $this->excelObj->getActiveSheet()->getStyle($index)->getFont()->setBold($select);
    }

    /**
     * 默认设置居中
     */
    public function setDefaultStyleAlignment()
    {
        $this->excelObj->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excelObj->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    }

    /**
     * 设置属性
     * @param array $array
     * @example :
     * [
     * 'creator'=>'创建人',
     * 'reviser'=>'最后修改人',
     * 'title'=>'标题',
     * 'subject'=>'题目',
     * 'description'=>'描述',
     * 'keywords'=>'关键字',
     * 'category'=>'种类'
     * ]
     */
    public function setAttribute($array)
    {
        foreach ($array as $key => $value) {
            switch ($key) {
                case 'creator':
                    call_user_func(array($this, 'setCreator'), $value);
                    break;
                case 'reviser':
                    call_user_func(array($this, 'setReviser'), $value);
                    break;
                case 'title':
                    call_user_func(array($this, 'setTitle'), $value);
                    break;
                case 'subject':
                    call_user_func(array($this, 'setSubject'), $value);
                    break;
                case 'description':
                    call_user_func(array($this, 'setDescription'), $value);
                    break;
                case 'keywords':
                    call_user_func(array($this, 'setKeywords'), $value);
                    break;
                case 'category':
                    call_user_func(array($this, 'setCategory'), $value);
                    break;
                default:
                    break;
            }
        }
    }

    /**
     * 设置当前的sheet
     * @param $index
     */
    public function setSheetIndex($index)
    {
        $this->excelObj->setActiveSheetIndex($index);
    }

    /**
     * 合并单元格
     * @param $rangeString
     */
    public function setMergeCells($rangeString)
    {
        $this->excelObj->getActiveSheet()->mergeCells($rangeString);
    }

    /**
     * 分离单元格
     * @param $rangeString
     */
    public function setUnMergeCells($rangeString)
    {
        $this->excelObj->getActiveSheet()->unmergeCells($rangeString);
    }

    /**
     * 冻结窗口
     * @param $pCell
     */
    public function freezePane($pCell)
    {
        $this->excelObj->getActiveSheet()->freezePane($pCell);
    }

    /**
     * 保护cell
     * @param $pCell
     */
    public function protectCells($pCell)
    {
        $this->excelObj->getActiveSheet()->getProtection()->setSheet(true);
        $this->excelObj->getActiveSheet()->protectCells($pCell, 'PHPExcel');
    }

    /**
     * 设置第几行为文本
     * @param $index
     */
    public function setTextFormat($index)
    {
        $this->excelObj->getActiveSheet()->getStyle($index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    }

    /**
     * 设置第几行为float货币形式￥
     * @param $index
     */
    public function setMoneyFloatFormat($index)
    {
        $this->excelObj->getActiveSheet()->getStyle($index)->getNumberFormat()->setFormatCode(self::FORMAT_CURRENCY_USD_SIMPLE);
    }

    /**
     * 设置某单元格的URL
     * @param $index
     * @param $url
     */
    public function setUrl($index, $url)
    {
        $this->excelObj->getActiveSheet()->getCell($index)->getHyperlink()->setUrl($url);
    }

    /**
     * 单元格换行
     * @param $rangeString
     */
    public function setWrapText($rangeString)
    {
        $this->excelObj->getActiveSheet()->getStyle($rangeString)->getAlignment()->setWrapText(true);
    }

    /**
     * 隐藏第几列
     * @param $index
     */
    public function setVisible($index)
    {
        $this->excelObj->getActiveSheet()->getColumnDimension($index)->setVisible(false);
    }

    /**
     * 自动过滤
     * @param $rangeString
     */
    public function setAutoFilter($rangeString)
    {
        $this->excelObj->getActiveSheet()->setAutoFilter($rangeString);
    }
}
